package be.vdab.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import be.vdab.entities.Genre;

public class GenreDAO {
	private static final String GENRE_SELECTIE = "Select * from genres order by genre asc";
	private static final String GENRE_NAAMSELECTIE = "Select genre from genres where genrenr=?";
	private final DatabaseUtil databaseUtil = new DatabaseUtil();

	public List<Genre> zoekGenre() {
		Connection connection = null;
		ResultSet resultSet = null;
		Statement statement = null;
		try {
			List<Genre> genres = new ArrayList<Genre>();
			connection = databaseUtil.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(GENRE_SELECTIE);
			while (resultSet.next()) {
				genres.add(resultSetRijNaarGenre(resultSet));
			}
			return genres;
		} catch (SQLException ex) {
			throw new DAOException("kan genre niet lezen uit de database" + ex);
		} finally {
			databaseUtil.close(resultSet, statement, connection);
		}
	}

	public String genreNaam(Long nummer) {
		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		try {
			connection = databaseUtil.getConnection();
			statement = connection.prepareStatement(GENRE_NAAMSELECTIE);
			statement.setLong(1, nummer);
			resultSet = statement.executeQuery();
			resultSet.next();
			String naam = resultSet.getString("Genre");
			return naam;
		} catch (SQLException ex) {
			throw new DAOException("kan genre niet lezen uit de database" + ex);
		} finally {
			databaseUtil.close(resultSet, statement, connection);
		}
	}

	private Genre resultSetRijNaarGenre(ResultSet resultSet)
			throws SQLException {
		return new Genre(resultSet.getInt("genreNr"),
				resultSet.getString("Genre"));
	}

}
